package db;

import db.TableField;

import java.io.File;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Connection;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import autoteam.Sports;
import db.SQLDataTypes;

/**
 * DataAccessObject provides an interface for interacting with a database.
 * Creating this object automatically creates a database with
 * the provided name, if it doesn't already exist.
 * The object provides means for connecting to the db and
 * commands such as select, update, or delete. See individual documentation
 * for details.
 * 
 * @author Enrique De Diego
 * 
 */
public class DataAccessObject
{
	private Connection dbConnection;
	private boolean isConnected;
	private String dbName;
	private DatabaseInfo dbInfo;
	
	public DataAccessObject()
	{
		this("rsdb");
	}
	
	public DataAccessObject(String dbName)
	{
		this.dbName = dbName;

		//load derby driver
		String driverName = "org.apache.derby.jdbc.EmbeddedDriver";
		loadDatabaseDriver(driverName);
		
		/*set the database system directory
		 *on the user's local machine */
		setDBSystemDir();
		
		/* Only create the database if it doesn't exist */
		if (!dbExists())
		{
			createDatabase();
		}
		
		try
		{
			dbConnection = DriverManager.getConnection("jdbc:derby:" + dbName);
		}
		catch (SQLException e) { e.printStackTrace(); }
		
		/*
		 * get access to the database info
		 * (for things such as retrieving column names)
		 */
		this.dbInfo = new DatabaseInfo(this);
		
		
		
		
	}
	
	public static void main(String[] args)
	{
		DataAccessObject dao = new DataAccessObject("rsdb");
		System.out.println(dao.getDatabaseLocation());
		System.out.println(dao.getDatabaseUrl());
		dao.connect();
		TableField lastname = new TableField("lastname",SQLDataTypes.VARCHAR);
		TableField firstname = new TableField("firstname",SQLDataTypes.VARCHAR);
		System.out.println("tableDropped: " + dao.dropTable("athletes"));
		System.out.println("tableCreated: " + dao.createTable("athletes", lastname, firstname));
		
		
		dao.addRecord("athletes", new ColumnData("firstname","jim"),
								  new ColumnData("lastname","halper"));
		dao.addRecord("athletes", new ColumnData("firstname","pam"),
				  				  new ColumnData("lastname","beasley"));
		
		List<DBRecord> recordList = dao.getAllRecords("athletes");
		
		for(DBRecord r : recordList)
			System.out.println(r.getFieldValue("firstname"));

		dao.deleteRecord("athletes", new ColumnData("firstname","pam"));
		
		System.out.println("=======");
		
		recordList = dao.getAllRecords("athletes");
		for(DBRecord r : recordList)
			System.out.println(r.getFieldValue("firstname"));
		
		ColumnData idCol = new ColumnData("id",0);
		ColumnData newFNameCol = new ColumnData("firstname","barry");
		ColumnData newLNameCol = new ColumnData("lastname","white");
		ColumnData[] identifiers = new ColumnData[1];
		identifiers[0] = idCol;
		ColumnData[] changes = new ColumnData[2];
		changes[0] = newFNameCol;
		changes[1] = newLNameCol;
		dao.updateRecord("athletes", identifiers, changes);
		
		recordList = dao.getAllRecords("athletes");
		for(DBRecord r : recordList)
			System.out.println(r.getFieldValue("firstname"));
		
		//create leagues table
		TableField leagueName = new TableField("leagueName",SQLDataTypes.VARCHAR);
		TableField sport = new TableField("sport",SQLDataTypes.VARCHAR);
		dao.dropTable("leagues");
		System.out.println("League table created: " + dao.createTable("leagues",leagueName,sport));
		dao.addRecord("leagues", new ColumnData("leagueName","Bacardi15"),
								 new ColumnData("sport",Sports.BASKETBALL.toString()));
		dao.addRecord("leagues", new ColumnData("leagueName","Orange18"),
								 new ColumnData("sport",Sports.BASEBALL.toString()));
		dao.addRecord("leagues", new ColumnData("leagueName","Blue32"),
				 new ColumnData("sport",Sports.FOOTBALL.toString()));
		
		
		//create coaches table
		
		System.out.println("Table dropped: " + dao.dropTable("coaches"));
		
		System.out.println("coach table created: " +
		dao.createTable("coaches", 
						new TableField("teamId",SQLDataTypes.INTEGER),
						new TableField("lastname",SQLDataTypes.VARCHAR),
						new TableField("firstname",SQLDataTypes.VARCHAR),
						new TableField("picturepath",SQLDataTypes.VARCHAR),
						new TableField("biography",SQLDataTypes.VARCHAR))
		);
		dao.addRecord("coaches", new ColumnData("teamId",0),
								 new ColumnData("lastname","dediego"),
								 new ColumnData("firstname","enrique"),
								 new ColumnData("picturepath","./trunk/img/profile_default.gif"),
								 new ColumnData("biography","default bio"));
		
		dao.addRecord("coaches", new ColumnData("teamId",1),
				 new ColumnData("lastname","cricket"),
				 new ColumnData("firstname","cricket"),
				 new ColumnData("picturepath","./trunk/img/profile_default.gif"),
				 new ColumnData("biography","default bio"));
		
		DBRecord temp = dao.getRecord("coaches", new ColumnData("lastname","cricket"));
		System.out.println("Gotten record: " + temp.getFieldValue("Picturepath"));
		
		//create players table
		TableField lastName		= new TableField("lastname", SQLDataTypes.VARCHAR);
		TableField firstName	= new TableField("firstname", SQLDataTypes.VARCHAR);
		TableField playerID		= new TableField("playerid", SQLDataTypes.INTEGER);
		TableField phone		= new TableField("phone", SQLDataTypes.VARCHAR);
		TableField email		= new TableField("email", SQLDataTypes.VARCHAR);
		TableField height		= new TableField("height", SQLDataTypes.INTEGER);
		TableField weight		= new TableField("weight", SQLDataTypes.INTEGER);
		TableField experience	= new TableField("experience", SQLDataTypes.INTEGER);
		TableField monday 		= new TableField("monday", SQLDataTypes.INTEGER);
		TableField tuesday 		= new TableField("tuesday", SQLDataTypes.INTEGER);
		TableField wednesday	= new TableField("wednesday", SQLDataTypes.INTEGER);
		TableField thursday		= new TableField("thursday", SQLDataTypes.INTEGER);
		TableField friday 		= new TableField("friday", SQLDataTypes.INTEGER);
		TableField skill		= new TableField("skill", SQLDataTypes.VARCHAR);
		TableField comment		= new TableField("comment", SQLDataTypes.VARCHAR);
		
		//System.out.println(dao.dropTable("players"));
		
		System.out.println("Players table: " + 
		dao.createTable("players", lastName, firstName, playerID, phone, email, height, weight, 
				experience, monday, tuesday, wednesday, thursday, friday, skill, comment)
		);
		
		List<DBRecord> playerRecs = dao.getAllRecords("players");
		for (DBRecord rec : playerRecs)
		{
			System.out.println(rec.getFieldValue("firstname")+" "+rec.getFieldValue("lastname"));
		}
		
		//TEAMS table
		System.out.println(dao.dropTable("teams"));
		System.out.println("Teams table: " + 
		dao.createTable("teams", new TableField("teamId",SQLDataTypes.INTEGER),
								 new TableField("teamName", SQLDataTypes.VARCHAR),
								 new TableField("players",SQLDataTypes.VARCHAR),
								 new TableField("coach",SQLDataTypes.INTEGER),
								 new TableField("coachkid",SQLDataTypes.INTEGER),
								 new TableField("practiceNight",SQLDataTypes.INTEGER),
								 new TableField("location", SQLDataTypes.VARCHAR),
								 new TableField("sport",SQLDataTypes.VARCHAR))
		);
		
		dao.addRecord("teams", new ColumnData("teamName","Gators"),
							   new ColumnData("players","0,1"),
							   new ColumnData("coach",0),
							   new ColumnData("coachKid",1),
							   new ColumnData("practiceNight",0),
							   new ColumnData("location", "Gainesville, FL"));
		
		
		dao.disconnect();
	}
	
	/*--------------------------------------------*
	 *-----Database Connection and Creation-------*
	 *--------------------------------------------*/
	
	/**
	 * Set the database's location on the user's machine
	 */
	private void setDBSystemDir()
	{
		// decide on the db system directory
		String dbDir = "./trunk/db/rdb";
		System.setProperty("derby.system.home", dbDir);

		// create the db system directory
		File fileSystemDir = new File(dbDir);
		fileSystemDir.mkdir();
	}
	
	/**
	 * 
	 * @return the Connection object for the database
	 */
	public Connection getDbConnection()
	{
		return dbConnection;
	}
	
	/**
	 * 
	 * @return String, location of database
	 */
	public String getDatabaseLocation()
	{
		String dbLocation = System.getProperty("derby.system.home") + "/"
				+ dbName;
		return dbLocation;
	}
	
	/**
	 * 
	 * @return String, (programmatic) URL of database
	 */
	public String getDatabaseUrl()
	{
		String dbUrl = "jdbc:derby:" + dbName;
		return dbUrl;
	}
	
	/**
	 * Load the Derby database driver that runs the show.<br>
	 * Be sure you've included the derby.jar file in your IDE's 
	 * include path
	 * @param driverName
	 */
	private void loadDatabaseDriver(String driverName)
	{
		// load Derby driver
		try
		{
			Class.forName(driverName);
		} 
		catch (ClassNotFoundException ex)
		{
			ex.printStackTrace();
		}

	}
	
	/**
	 * Check whether the database already exists
	 * @return true if it exists
	 */
	private boolean dbExists()
	{
		boolean bExists = false;
		String dbLocation = getDatabaseLocation();
		File dbFileDir = new File(dbLocation);
		if (dbFileDir.exists())
		{
			bExists = true;
		}
		return bExists;
	}
	
	/**
	 * Create the database (after checking whether it exists or not)
	 * @return true if it was successful
	 */
	private boolean createDatabase()
	{
		boolean bCreated = false;

		String dbUrl = "jdbc:derby:" + dbName + ";create=true";

		try
		{
			dbConnection = DriverManager.getConnection(dbUrl);
			bCreated = true;
		} 
		catch (SQLException ex)
		{
			
		}
		return bCreated;
	}
	
	//-------------------------------------
	//-----------Database Actions--------//
	//-------------------------------------
	
	/**
	 * Connect to database specified by dbName
	 */
	public boolean connect()
	{
		String dbUrl = "jdbc:derby:" + dbName;
		try
		{
			dbConnection = DriverManager.getConnection(dbUrl);
			isConnected = dbConnection != null;
		}
		catch (SQLException e)
		{
			isConnected = false;
			System.out.println("WARNING: Database connection failed!");
		}
		return isConnected;
	}
	
	/**
	 * Disconnect from the database
	 */
	public void disconnect()
	{
		if (isConnected)
		{
			String dbUrl = getDatabaseUrl() + ";shutdown=true";
			try
			{
				DriverManager.getConnection(dbUrl);
				isConnected = false;
			} 
			catch (SQLException ex)
			{
				//System.out.println("WARNING: Database was NOT disconnected!");
			}
			
		}
	}
	/**
	 * Creates a table with the specified fields<br>
	 * <b>NOTE:</b> createTable inputs an ID field by default defined as:<br>
	 * <em>ID INTEGER NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY (START WITH 0, INCREMENT BY 1)</em>
	 * @param tableName - String, name of the table
	 * @param fields - TableField(s), field information 
	 * @return true if table was created, false if there was an error
	 */
	public boolean createTable(String tableName, TableField...fields)
	{
		boolean tableCreated = false;
		Statement createStatement = null;
		
		//begin creation SQL
		String createString = "CREATE TABLE APP." + tableName + " (";
		
		//include ID field in every table
		TableField idField = new TableField("id","INTEGER NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY (START WITH 0, INCREMENT BY 1)");
		createString += idField.getFieldName() + " " + idField.getProps();
		createString += ", ";
		
		for (int i = 0; i < fields.length; i++)
		{
			//append fields
			createString += fields[i].getFieldName() + " " + fields[i].getDataType().toString();
			
			//decide whether or not to add closing parenthesis
			if (i == fields.length - 1)
				createString += ")";
			else
				createString += ", ";
		}
		
		try
		{
			createStatement = dbConnection.createStatement();
			createStatement.execute(createString);
			tableCreated = true;
		}
		catch (SQLException e)
		{
			//ignore the exception if it's complaining
			//about inserting the same table
			if (!e.getSQLState().equals("X0Y32"))
				e.printStackTrace();
		}
		System.out.println(createString);
		return tableCreated;
	}
	
	/**
	 * Drop (delete) (permanently) a table in the database
	 * @param tableName - the table to be dropped
	 * @return true, if the drop was successful (sh*t happens)
	 */
	public boolean dropTable(String tableName)
	{
		Statement dropStatement = null;
		String dropString = "DROP TABLE " + tableName;
		boolean tableDropped = false;
		try
		{
			dropStatement = dbConnection.createStatement();
			dropStatement.execute(dropString);
			tableDropped = true;
			System.out.println(dropString);
		}
		catch (SQLException e) { e.printStackTrace(); };
		
		return tableDropped;
	}
	
	/**
	 * Get all the data of a table in the database
	 * @param tableName - the table whose data you'd like to receive
	 * @return - a List of Records with the data
	 */
	public List<DBRecord> getAllRecords(String tableName)
	{
		List<DBRecord> recordList = new ArrayList<DBRecord>();
		String selectAllString = "SELECT * FROM " + tableName;
		
		try
		{
			Statement selectAllStmt = dbConnection.createStatement();
			ResultSet rs = selectAllStmt.executeQuery(selectAllString);
			String[] colNames = dbInfo.getTableColNames(tableName);
			
			while (rs.next())
			{
				DBRecord temp = new DBRecord();

				for(int i = 0; i < colNames.length; i++)
				{
					temp.addField(colNames[i], rs.getObject(i+1));
				}
				recordList.add(temp);
			}
		}
		catch (SQLException e) { e.printStackTrace(); }
		
		return recordList;
	}
	
	/**
	 * Retrieve a Record from the database<br>
	 * The record will be fully qualified with all available info
	 * @return Record - the record being searched for
	 */
	public DBRecord getRecord(String tableName, ColumnData...cols)
	{
		DBRecord record = new DBRecord();
		String selectString = "";
		selectString += "SELECT * FROM " + tableName + " WHERE ";
		for(int i = 0; i < cols.length; i++)
		{
			selectString += cols[i].getColName().toUpperCase();
			selectString += "=";
			
			//I'm sorry, Dave ;_;
			if(cols[i].getColValue() instanceof Integer ||
					cols[i].getColValue() instanceof Double)
				selectString += cols[i].getColValue();
			else 
				selectString += "'" + cols[i].getColValue() + "'";
			
			if (i != cols.length - 1)
				selectString += " AND ";
		}
		
		//hold results
		ResultSet result = null;
		String[] colNames = null;
		try
		{
			Statement selectStmt = dbConnection.createStatement();
			result = selectStmt.executeQuery(selectString);
			
			colNames = dbInfo.getTableColNames(tableName);

			while(result.next())
			{
				for(int i = 0; i < colNames.length; i++)
				{
					record.addField(colNames[i], result.getObject(i+1));
				}
			}
		}
		catch (SQLException e) { e.printStackTrace(); }
		
		System.out.println(selectString);
		return record;
	}
	
	/**
	 * Add a row to a table
	 * @param tableName - the table where you want to add the row
	 * @return - the Record corresponding to the row inserted
	 */
	public void addRecord(String tableName, ColumnData...cols)
	{
		
		/*
		 * Construct: "INSERT INTO tableName "
			+ "   (field1, field2, ..., fieldN) "
			+ "VALUES (?[,?,?,?...])"
		 */
		//TODO: create Record signature
		String addString = "";
		addString += "INSERT INTO " + tableName + " (";
		
		//add field names
		for (int i = 0; i < cols.length; i++)
		{
			addString += cols[i].getColName().toUpperCase();
			
			if (i == cols.length - 1)
				addString += ")";
			else
				addString += ", ";
		}
		
		
		//add VALUES
		addString += " VALUES (";
		for(int i = 0; i < cols.length; i++)
		{
			if (cols[i].getColValue() instanceof Integer)
			{
				addString += cols[i].getColValue();
			}
			else
			{
				addString += "'" + cols[i].getColValue() + "'";
			}
			if (i == cols.length - 1)
				addString += ")";
			else
				addString += ", ";
		}
		
		try
		{
			Statement addStatement = dbConnection.createStatement();
			addStatement.execute(addString);
		}
		catch (SQLException e) { e.printStackTrace(); }
		System.out.println(addString);
	}
	
	/**
	 * Update a row in a table
	 * @param tableName - the table in which the row is located
	 * @param identifiers A ColumnData[] providing columns by which you wish<br>
	 * to identify the row
	 * @param changes A ColumnData[] providing the changed values of columns
	 */
	public void updateRecord(String tableName, ColumnData[] identifiers, ColumnData[] changes)
	{
		//TODO
		/*
		 * Construct: UPDATE tableName SET column = newExpression WHERE
		 * column = originalExpression
		 */
		
		String updateString = "UPDATE " + tableName + " SET ";
		/* SET clause */
		for (int i = 0; i < changes.length; i++)
		{
			if (changes[i] != null)
			{
				updateString += changes[i].getColName();
				updateString += "=";
				if (changes[i].getColValue() instanceof String)
					updateString += "'" + changes[i].getColValue() + "'";
				else
					updateString += changes[i].getColValue();
				
				if (i != changes.length - 1)
					updateString += ", ";
			}
					
		}
		/* WHERE clause */
		updateString += " WHERE ";
		for (int i = 0; i < identifiers.length; i++)
		{
			if (identifiers[i] != null)
			{
				updateString += identifiers[i].getColName();
				updateString += "=";
				if (identifiers[i].getColValue() instanceof String)
					updateString += "'" + identifiers[i].getColValue() + "'";
				else
					updateString += identifiers[i].getColValue();
				
				if (i != identifiers.length - 1)
					updateString += " AND ";
			}
		}
		
		/* Make some magic happen */
		try
		{
			Statement updateStmt = dbConnection.createStatement();
			updateStmt.execute(updateString);
			System.out.println(updateString);
		}
		catch (SQLException e) { e.printStackTrace(); }
		
	}
	
	/**
	 * Delete a row in a table
	 * @param tableName - the table in which the row is located
	 */
	public void deleteRecord(String tableName, ColumnData...cols)
	{
		//TODO
		/*
		 * Constructing string:
		 * DELETE FROM tableName WHERE col=value
		 */
		String deleteString = "DELETE FROM " + tableName + " WHERE ";
		for(int i = 0; i < cols.length; i++)
		{
			deleteString += cols[i].getColName().toUpperCase();
			deleteString += "=";
			
			//I'm sorry, Dave ;_;
			if(cols[i].getColValue() instanceof Integer ||
					cols[i].getColValue() instanceof Double)
				deleteString += cols[i].getColValue(); 
			else 
				deleteString += "'" + cols[i].getColValue() + "'";
			
			if (i != cols.length - 1)
				deleteString += " AND ";
		}

		try
		{
			Statement deleteStmt = dbConnection.createStatement();
			deleteStmt.execute(deleteString);
		}
		catch (SQLException e) { e.printStackTrace(); }
		
		System.out.println(deleteString);
	}
	
	
	/*--------------------------------------------*
	 *------------Database Meta Data--------------*
	 *--------------------------------------------*/
	public DatabaseInfo getDBInfo()
	{
		return dbInfo;
	}
	
}
